<?php
defined('RUN_KATANA') or die('Restricted access');
/*
# Name: Database.class.php
# File Description: MySQL Class to allow easy and clean access to common mysql commands
# Author: ricocheting
# Web: http://www.ricocheting.com/scripts/
# Update: 2/2/2009
# Version: 2.1
# Copyright 2003 ricocheting.com

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>.

$db = new Database($config['server'],$config['user'],$config['pass'],$config['database'],$config['tablePrefix']);
*/
class Database 
{
	private $Server; //database Server
	private $User; //database login name
	private $Pass; //database login password
	private $DbName; //database name
	private $PreFix; //table prefix
	private $DBType; //database Type
	
//	#######################
	//internal info
	public $Record = array();
	
	public $Log;
	public $Error;
	public $Errno = 0;
	
	//table name affected by SQL query
	public $field_table= "";
	
	//number of rows affected by SQL query
	public $AffectedRows = 0;
	
	public $LinkId = 0;
	public $QueryId = 0;


	public $Sql;
	public $TTL;
	public $Name;
	public $Filename;
	public $DataTs;
	
	public $cnn_id;
	
	public $dir;
	
	
	public $fso;
	
	public $cnn;
	
	public $last_id;

	
//	#-#############################################
//	# desc: constructor
	function Database()
	{
		if(defined('DB_SERVER') && defined('DB_NAME') && defined('DB_UNAME') && defined('DB_PASS') && defined('DB_PERFIX'))
		{
			$this->Server=DB_SERVER;
			$this->User=DB_UNAME;
			$this->Pass=DB_PASS;
			$this->DbName=DB_NAME;
			$this->PreFix=DB_PERFIX;
			
			if(defined('DB_TYPE'))
			{ $this->DBType=DB_TYPE; }
			else
			{ $this->DBType='mysql'; }
			
			$this->TTL = 0;
			$this->DataTs = 0;
		}
	}
//	#-#constructor()
	
	
//	#-#############################################
//	# desc: connect and select database using vars above
//	# Param: $new_link can force connect() to open a new link, even if mysql_connect() was called before with the same parameters
	function connect($new_link=false) 
	{
		$this->LinkId=@mysql_connect($this->Server, $this->User, $this->Pass, $new_link);
	
		if (!$this->LinkId) //open failed
		{ $this->oops("Could not connect to server: <b>$this->Server</b>."); }
	
		if(!@mysql_select_db($this->DbName, $this->LinkId)) //no database
		{ $this->oops("Could not open database: <b>$this->DbName</b>."); }
		
		$this->Server='';
		$this->User='';
		$this->Pass='';
		$this->DbName='';
	}
//	#-#connect()
	
//	#-#############################################
//	# desc: close the connection
	function close() 
	{ 
		if(!mysql_close($this->LinkId))
		{ $this->oops("Connection close failed."); }
	}
//	#-#close()
	
	
//	#-#############################################
//	# Desc: escapes characters to be mysql ready
//	# Param: string
//	# returns: string
	function escape($string) 
	{
		if(get_magic_quotes_gpc()) $string = stripslashes($string);
		return mysql_real_escape_string($string);
	}
//	#-#escape()

	
//	#-#############################################
//	# Desc: executes SQL query to an open connection
//	# Param: (MySQL query) to execute
//	# returns: (QueryId) for fetching results etc
	function query($sql) 
	{
		// do query
		$this->QueryId = @mysql_query($sql, $this->LinkId);
	
		if (!$this->QueryId) 
		{ $this->oops("<b>MySQL Query fail:</b> $sql"); }
		
		$this->AffectedRows = @mysql_affected_rows();
	
		return $this->QueryId;
	}
//	#-#query()


//	#-#############################################
//	# desc: fetches and returns results one line at a time
//	# param: QueryId for mysql run. if none specified, last used
//	# return: (array) fetched record(s)
	function fetch_array($qId=-1) 
	{
		// retrieve row
		if ($QueryId != -1) 
		{ $this->QueryId=$qId; }
	
		if (isset($this->QueryId)) 
		{ $this->Record = @mysql_fetch_assoc($this->QueryId); }
		else
		{ $this->oops("Invalid QueryId: <b>$this->QueryId</b>. Records could not be fetched."); }
	
		// unescape Records
		if($this->Record)
		{
			$this->Record=array_map("stripslashes", $this->Record);
			//foreach($this->Record as $key=>$val) {
			//    $this->Record[$key]=stripslashes($val);
			//}
		}
		return $this->Record;
	}
//	#-#fetch_array()

	
//	#-#############################################
//	# desc: returns all the results (not one row)
//	# param: (MySQL query) the query to run on server
//	# returns: assoc array of ALL fetched results
	function fetch_all_array($sql) 
	{
		$qId = $this->query($sql);
		$out = array();
	
		while($row = $this->fetch_array($qId, $sql))
		{ $out[] = $row; }
	
		$this->free_result($qId);
		return $out;
	}
//	#-#fetch_all_array()

	
//	#-#############################################
//	# desc: frees the resultset
//	# param: query_id for mysql run. if none specified, last used
	function free_result($qId=-1) 
	{
		if ($qId!=-1) 
		{ $this->QueryId=$qId; }
		
		if(!@mysql_free_result($this->QueryId)) 
		{ $this->oops("Result ID: <b>$this->QueryId</b> could not be freed."); }
	}
//	#-#free_result()

	
//	#-#############################################
//	# desc: does a query, fetches the first row only, frees resultset
//	# param: (MySQL query) the query to run on server
//	# returns: array of fetched results
	function query_first($query_string) 
	{
		$qId = $this->query($query_string);
		$out = $this->fetch_array($qId);
		$this->free_result($qId);
		return $out;
	}
//	#-#query_first()


//	#-#############################################
//	# desc: does an update query with an array
//	# param: table (no prefix), assoc array with data (doesn't need escaped), where condition
//	# returns: (query_id) for fetching results etc
	function query_update($table, $data, $where='1') 
	{
		$q="UPDATE `".$this->PreFix.$table."` SET ";
	
		foreach($data as $key=>$val) 
		{
			if(strtolower($val)=='null') $q.= "`$key` = NULL, ";
			elseif(strtolower($val)=='now()') $q.= "`$key` = NOW(), ";
			else $q.= "`$key`='".$this->escape($val)."', ";
		}
	
		$q = rtrim($q, ', ') . ' WHERE '.$where.';';
	
		return $this->query($q);
	}
//	#-#query_update()

	
//	#-#############################################
//	# desc: does an insert query with an array
//	# param: table (no prefix), assoc array with data
//	# returns: id of inserted Record, false if Error
	function query_insert($table, $data) 
	{
		$q="INSERT INTO `".$this->PreFix.$table."` ";
		$v=''; $n='';
	
		foreach($data as $key=>$val) 
		{
			$n.="`$key`, ";
			if(strtolower($val)=='null') $v.="NULL, ";
			elseif(strtolower($val)=='now()') $v.="NOW(), ";
			else $v.= "'".$this->escape($val)."', ";
		}
	
		$q .= "(". rtrim($n, ', ') .") VALUES (". rtrim($v, ', ') .");";
	
		if($this->query($q))
		{
			//$this->free_result();
			return mysql_insert_id();
		}
		else return false;
	
	}
//	#-#query_insert()


//	#-#############################################
//	# desc: throw an Error message
//	# param: [optional] any custom Error to display
	function oops($msg='') 
	{
		if($this->LinkId>0)
		{
			$this->Error=mysql_error($this->LinkId);
			$this->Errno=mysql_errno($this->LinkId);
		}
		else
		{
			$this->Error=mysql_error();
			$this->Errno=mysql_errno();
		}
		
		$out  = '<table align="center" border="1" cellspacing="0" style="background:white;color:black;width:80%;">';
		$out .=	'<tr><th colspan=2>Database Error</th></tr>';
		$out .=	'<tr><td align="right" valign="top">Message:</td><td>'.$msg.'</td></tr>';
		if(strlen($this->Error) > 0) 
		{ $out .=	'<tr><td align="right" valign="top" nowrap>MySQL Error:</td><td>'.$this->Error.'</td></tr>'; }
		
		$out .=	'<tr><td align="right">Date:</td><td>'.date("l, F j, Y \a\\t g:i:s A").'</td></tr>';
		$out .=	'<tr><td align="right">Script:</td><td><a href="'.@$_SERVER['REQUEST_URI'].'">'.@$_SERVER['REQUEST_URI'].'</a></td></tr>';
		if(strlen(@$_SERVER['HTTP_REFERER'])>0) 
		{ $out .=	'<tr><td align="right">Referer:</td><td><a href="'.@$_SERVER['HTTP_REFERER'].'">'.@$_SERVER['HTTP_REFERER'].'</a></td></tr>'; }
		$out .=	'</table>';
		
		echo $out;
		return false;
	}
	
	function fetch($sql='', $ttl=0, $name='') 
	{
		$this->Log .= '---------------------------------<br />fetch() called<br />';
		$this->AffectedRows = 0;
		if (!$sql) 
		{ 
			$this->Log .= "OOPS: You need to pass a SQL statement!<br />";
			return false;
		}
		
		$this->Sql = $sql;
		$this->TTL = $ttl;
		$this->Name = $name;
		$this->Log .= 'SQL: '.$this->Sql.'<br />';
		
		$data = '';
		if ($this->TTL == 0) 
		{ return $data = $this->getFromDB(); } 
		else 
		{
			if (strlen(trim($this->Name)) == 0) 
			{ $this->Name = MD5($this->Sql); }
			
			$this->Filename = CACHE_PATH."db_".$this->Name.'.dat';
			
			$this->Log .= "Filename: ".$this->Filename."<br />";
			$this->getFileTs();
			
			if ($this->TTL == 'daily') 
			{
				if(date('Y-m-d', $this->DataTs) != date('Y-m-d',time())) 
				{
					$this->Log .= "cache has expired<br />";
					if (!$data = $this->getFromDB()) { return false; }
					if (!$this->saveToCache($data)) { return false; }
					return $data;
				} 
				else 
				{ return $data = $this->getFromCache(); }
			} 
			else 
			{
				if ((time() - $this->DataTs) >= $this->TTL) 
				{
				  $this->Log .= "cache has expired<br />";
				  if (!$data = $this->getFromDB()) { return false; }
				  if (!$this->saveToCache($data)) { return false; }
				  return $data;
				} 
				else 
				{  return $data = $this->getFromCache(); }
			}
		}
	}
	
	function getFileTs() 
	{
		$this->Log .= "getFileTs() called<br />";
		if (!file_exists($this->Filename)) 
		{
			$this->DataTs = 0;
			$this->Log .= $this->Filename." does not exist<br />";
			return false;
		}
		$this->DataTs = filemtime($this->Filename);
		return true;
	}
	
	function getFromCache() 
	{
		$this->Log .= "getFromCache() called<br />";
		if (!$x = @file_get_contents($this->Filename)) 
		{
			$this->Log .= "Could not read ".$this->Filename."<br />";
			return false;
		}
		if (!$data = unserialize($x)) 
		{
			$this->Log .= "getFromCache() failed<br />";
			return false;
		}
		return $data;
	}
	
	function saveToCache($data) 
	{
		$this->Log .= "saveToCache() called<br />";
		//create file pointer
		if (!$fp=@fopen($this->Filename,"w")) 
		{
			$this->Log .= "Could not open ".$this->Filename."<br />";
			return false;
		}
		//write to file
		if (!@fwrite($fp,serialize($data))) 
		{
			$this->Log .= "Could not write to ".$this->Filename."<br />";
			fclose($fp);
			return false;
		}
		//close file pointer
		fclose($fp);
		return true;
	}
	
	function getFromDB() 
	{
		$this->Log .= "getFromDB() called<br />";
		ini_set('memory_limit', '-1');
		if(!$this->LinkId)
		{ $this->connect(); }
		switch($this->DBType) 
		{
			case "mysql":
				if (!$res = @mysql_query($this->Sql, $this->LinkId)) 
				{
					$this->Log .= "Query execution failed.<br />";
					$this->Log .= mysql_error()."<br />";
					return false;
				}
				while($row = mysql_fetch_array($res, MYSQL_ASSOC)) 
				{ $data[] = $row; }
				break;
				
			case "postgres":
				if (!$res = @pg_query($this->LinkId, $this->Sql)) 
				{
					$this->Log .= "Query execution failed.<br />";
					$this->Log .= pg_last_error()."<br />";
					return false;
				}
				if(!$data = @pg_fetch_all($res)) 
				{
					$this->Log .= "getFromDB() failed<br />";
					return false;
				}
			break;
				case "mssql":
				if(!$res = @mssql_query($this->Sql, $this->LinkId)) 
				{
					$this->Log .= "Query execution failed.<br />";
					$this->Log .= mssql_error()."<br />";
					return false;
				}
				while ($row = mssql_fetch_array($res)) 
				{ $data[] = $row; }
			break;
		}
		return $data;
	}
	
	function addTablesCombination($field, $value, $field2, $value2, $combtable)
	{
		if(($value > 0) && ($value2 > 0) && ($field != '') && ($field2 != '') && ($combtable != ''))
		{
			$found = 0;
			$select = 'SELECT id FROM '.DB_PERFIX.$combtable.' WHERE '.$field.'='.$value.' AND '.$field2.'='.$value2.';';
			$result = mysql_query($select);
			while($row = mysql_fetch_array($result))
			{ $found = $row['id']; }
			if($found == 0)
			{
				$insert = 'INSERT INTO '.DB_PERFIX.$combtable.' (`'.$field.'`, `'.$field2.'`)';
				$insert .= 'VALUES ('.$value.', '.$value2.');';
				$result = mysql_query($insert);
			}
			return $result;
		}
	}
	
	function remTablesCombination($field, $value, $field2, $value2, $combtable)
	{
		if(($value > 0) && ($value2 > 0) && ($field != '') && ($field2 != '') && ($combtable != ''))
		{
			$delete = 'DELETE FROM '.DB_PERFIX.$combtable.' WHERE '.$field.'='.$value.' AND '.$field2.'='.$value2.';';
			$result = mysql_query($delete);
			mysql_query('OPTIMIZE TABLE '.DB_PERFIX.$combtable.';');
			return $result;
		}
	}
}
?>
